
insert overwrite table jms_dm.dm_terminal_tran_direct_delivery_send_sum_dt partition(dt)
--转运直送发件维度汇总
select
      end_provider_id                            --省份id
     ,max(end_provider_name) asend_provider_name  --省名字
     ,end_agent_code                              --代理区code
     ,max(end_agent_name) as end_agent_name       --代理区名字
     ,end_center_code                             --末端中心code
     ,max(end_center_name) as end_center_name     --末端中心名字
     ,end_city_id                                 --城市id
     ,max(end_city_name) as end_city_name         --末端城市
     ,end_area_id                                 --末端区县id
     ,max(end_area_name) as end_area_name         --末端区县
     ,final_sign_network_code                     --规划签收网点code
     ,max(final_sign_network_name) as final_sign_network_name   --规划签收网点
     ,package_type                                --包类型
     ,second_package_code                         --二段吗
     ,third_package_code                          --三段码
     ,sum(waybill_sum) as waybill_sum             --运单量
     ,count(1)  as package_num                    --包数
     ,sum(third_code_right_sum) as third_code_right_sum         --三段码准确的量
     ,sum(arrival_send_sum) as arrival_send_sum                 --一键到出量
     ,sum(aging_sign_sum) as aging_sign_sum                     --准点签收量
     ,sum(ass_sum) as ass_sum                                   --错分量
     ,sum(send_noarrival_sum) as send_noarrival_sum             --有发无到总量
     ,sum(center_send_sign_hours) as center_send_sign_hours     --中心发发件时间到时效签收时间
     ,sum(no_send_sign_cnt) as no_send_sign_cnt                 --发签缺失量
     ,sum(deliver_sign_hours) as deliver_sign_hours             --派签时长
     ,sum(deliver_sign_loss_cnt) as deliver_sign_loss_cnt       --派签缺失量
     ,dt as date_time                                           --业务日期   现在是集包扫描时间
     ,end_franchisee_code   --加盟商code
     ,max(end_franchisee_name) as end_franchisee_name   --加盟商
     ,dt                                                        --分区日期
from (
    select
      end_provider_id        --省份id
     ,max(end_provider_name) end_provider_name  --省名字
     ,end_agent_code                                --代理区code
     ,max(end_agent_name) as end_agent_name       --代理区名字
     ,end_center_code         --末端中心code
     ,max(end_center_name) as end_center_name   --末端中心名字
     ,end_city_id                                 --城市id
     ,max(end_city_name) as end_city_name      --末端城市
     ,end_area_id          --末端区县id
     ,max(end_area_name) as end_area_name   --末端区县
     ,end_franchisee_code   --加盟商code
     ,max(end_franchisee_name) as end_franchisee_name   --加盟商
     , send_next_station_code as final_sign_network_code               --规划签收网点code
     ,max(send_next_station_name) as final_sign_network_name   --规划签收网点
     ,package_type         --包类型
     ,second_package_code        --二段吗
     ,third_package_code         --三段码
     ,count(1) as waybill_sum  --运单量
     ,package_code     --包数
     ,sum(is_right_third_code) as third_code_right_sum                     --三段码准确的量
     ,sum(is_arrival_send) as arrival_send_sum                       --一键到出量
     ,sum(if(aging_sign_time<=final_plan_sign_time,1,0)) as aging_sign_sum     --准点签收量
     ,sum(is_ass) as ass_sum                                                   --错分量
     ,sum(is_send_noarrival) as send_noarrival_sum                              --有发无到总量
     ,sum(center_send_sign_hours) as center_send_sign_hours                      --中心发发件时间到时效签收时间
     ,sum(if(center_scan_user_code is null or aging_sign_time is null,1,0 )) as no_send_sign_cnt   --发签缺失量
     ,sum(deliver_sign_hours) as deliver_sign_hours                              --派签时长
     ,sum(if(deliver_time is null or aging_sign_time is null,1,0)) as deliver_sign_loss_cnt   --派签缺失量
     , dt        --分区日期  --集包扫描时间作为分区日期
from jms_dm.dm_terminal_tran_direct_delivery_detail_dt
where dt>=date_sub('{{ execution_date | cst_ds }}',29)
    and dt<='{{ execution_date | cst_ds }}'
group by end_provider_id,
         end_agent_code,
         end_center_code,
         end_city_id,
         end_area_id,
         end_franchisee_code,
         send_next_station_code,
         package_type,
         second_package_code,
         third_package_code,
         package_code,
         dt
         )tmp
group by end_provider_id,
         end_agent_code,
         end_center_code,
         end_city_id,
         end_area_id,
         end_franchisee_code,
         final_sign_network_code,
         package_type,
         second_package_code,
         third_package_code,
         dt
distribute by 10
;